package leetcode;

/**
 * @创建人 MBY381
 * @创建时间 2022/4/7 21:25
 * @描述 公司的主管们感兴趣的是公司每个部门中谁赚的钱最多。一个部门的 高收入者 是指一个员工的工资在该部门的 不同 工资中 排名前三 。
 * <p>
 * 编写一个SQL查询，找出每个部门中 收入高的员工 。
 * <p>
 * 以 任意顺序 返回结果表。
 * <p>
 * 查询结果格式如下所示。
 * <p>
 * 来源：力扣（LeetCode）
 * 链接：https://leetcode-cn.com/problems/department-top-three-salaries
 * 著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。
 */
public class Solution185 {
    /**
     * SELECT Department.NAME AS Department,Employee.Name AS Employee,Employee.salary AS Salary
     * FROM Employee
     * INNER JOIN DEPARTMENT ON Employee.DEPARTMENTID=DEPARTMENT.ID
     *
     * order BY Salary DESC
     * LIMIT 0,3;
     *
     * 错误版本，因为薪水可能相同
     *
     * 正确版本，但是MYSQL不支持IN中有LIMIT
     * “This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'”
     *
     * SELECT Department.NAME AS Department,Employee.Name AS Employee,Employee.salary AS Salary
     * FROM Employee
     * INNER JOIN DEPARTMENT ON Employee.DEPARTMENTID=DEPARTMENT.ID
     * WHERE Salary IN(SELECT DISTINCT Salary FROM Employee ORDER BY SALARY LIMIT 0,3)
     * order BY Salary DESC
     *
     *
     * 官方方法，看球不懂
     * SELECT
     *     d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
     * FROM
     *     Employee e1
     *         JOIN
     *     Department d ON e1.DepartmentId = d.Id
     * WHERE
     *     3 > (SELECT
     *             COUNT(DISTINCT e2.Salary)
     *         FROM
     *             Employee e2
     *         WHERE
     *             e2.Salary > e1.Salary
     *                 AND e1.DepartmentId = e2.DepartmentId
     *         )
     * ;
     *

     */
}
